<?php

namespace Icsoc\DataBundle\Model;

use Doctrine\DBAL\Portability\Connection;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Guzzle\Http\Client;
use Elasticsearch\Client as EsClient;

/**
 * Class ReportModel
 *
 * @package Icsoc\DataBundle\Model
 */
class ReportModel extends BaseModel
{
    /** @var \Doctrine\DBAL\Connection dbal */
    private $dbal;

    /** @var \Doctrine\DBAL\Connection dbal */
    private $cdrDbal;

    /**
     * @param ContainerInterface $container
     */
    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        $this->dbal = $this->container->get('doctrine.dbal.default_connection');
        $this->cdrDbal = $this->container->get('doctrine.dbal.cdr_connection');
    }

    /**
     * 呼入明细
     *
     * @param array $param
     *
     * @return array
     */
    public function getCallinListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $where .= " AND caller LIKE '%".$info['filter']['caller']."%'";
            }

            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $where .= " AND called LIKE '%".$info['filter']['called']."%'";
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }
            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num = :server_num";
                $condition['server_num'] = $info['filter']['server_num'];
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_incdr '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_incdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_name,group_id,call_id,server_num,caller,called,ag_num,que_name,start_time,quein_time, ".
                " caller_areacode,caller_areaname,called_areaname,called_areacode, ".
                " conn_time,end_time,ivr_secs,wait_secs,conn_secs,all_secs,`result` ".
                "FROM win_incdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].'
                LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_name,group_id,call_id,server_num,caller,called,ag_num,que_name,start_time,quein_time,".
                "caller_areacode,caller_areaname,called_areaname,called_areacode,".
                " conn_time,end_time,ivr_secs,wait_secs,conn_secs,all_secs,`result` "
                ." FROM win_incdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_incdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_name,group_id,call_id,server_num,caller,`called`,ag_num,que_name,start_time,quein_time,".
                "caller_areacode,caller_areaname,called_areaname,called_areacode,".
                " conn_time,end_time,ivr_secs,wait_secs,conn_secs,all_secs,`result` "
                ." FROM win_incdr AS wi INNER JOIN(".
                "SELECT id from win_incdr ".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['quein_time'] = $v['quein_time'] ? date("Y-m-d H:i:s", $v['quein_time']) : "";
            $gridData[$k]['conn_time'] = $v['conn_time'] ? date("Y-m-d H:i:s", $v['conn_time']) : "";
            $gridData[$k]['end_time'] = $v['end_time'] ? date("Y-m-d H:i:s", $v['end_time']) : "";
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 呼入明细报表elasticsearch搜索从mongodb中取值
     *
     * @param array $param
     * @return array
     */
    public function getCallinListDataFromElasticsearch(array $param = array()) {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        $big = empty($param['big']) ? false : $param['big'];
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        //$field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];

        $condition['term']['vcc_id'] = $vccId;
        $type = "win_incdr";
        $condition = array(
            'fixup' => array(
                'index' => 'incdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );

        $startTime = isset($info['filter']['start_time']) ? $info['filter']['start_time'] : date('Y-m-d');
        $endTime = isset($info['filter']['end_time']) ? $info['filter']['end_time'] : date('Y-m-d').' 23:59:59';
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($startTime, $endTime, 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => 0);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $condition['term']['caller'] = array('type' => 'wildcard', 'value' => $info['filter']['caller']);
            }

            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $condition['term']['called'] = array('type' => 'wildcard', 'value' => $info['filter']['called']);
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }
            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => $info['filter']['group_id']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'match', 'value' => $info['filter']['server_num']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $condition['export'] = empty($info['export']) ? 0 : 1;
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }
        $rows = $this->getDataFromESAndMongo($condition, 'win_incdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['call_id'] = (string) $item['call_id'];
            $gridData[$k]['start_time'] = $item['start_time'] ? date("Y-m-d H:i:s", $item['start_time']) : "";
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
            $gridData[$k]['quein_time'] = $item['quein_time'] ? date("Y-m-d H:i:s", $item['quein_time']) : "";
            $gridData[$k]['conn_time'] = $item['conn_time'] ? date("Y-m-d H:i:s", $item['conn_time']) : "";
            $gridData[$k]['caller'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($item['caller'], $cusPhoneAuthority);
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 获取Ivr轨迹
     *
     * @param array $param
     *
     * @return array
     */
    public function getIvrPathData(array $param = array())
    {
        $vccId = empty($param['vcc_id']) ? 0 : $param['vcc_id'];
        $infos = empty($param['data']) ? '' : $this->purifyHtml($param['data']);
        if (empty($vccId)) {
            return array('code' => 401, 'message' => '企业ID不能为空');
        }

        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 402, 'message' => 'info格式非json');
            }
        }
        $where = ' w.vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $where .= " AND ag_id = :ag_id";
                $agId = (int) $info['filter']['ag_num'];
                $condition['ag_id'] = $agId;
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $where .= " AND caller LIKE '%".$info['filter']['caller']."%'";
            }

            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $where .= " AND called LIKE '%".$info['filter']['called']."%'";
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }
            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num = :server_num";
                $condition['server_num'] = $info['filter']['server_num'];
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        try {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT w.call_id,p.ivr_path,i.ivr_info FROM win_incdr w
                LEFT JOIN win_ivr_path p ON w.call_id = p.call_id
                LEFT JOIN win_ivr i ON i.ivr_code = p.ivr_code WHERE $where LIMIT 10000",
                $condition
            );
        } catch (\Exception $e) {
            $logger = $this->container->get('logger');
            $logger->error($e->getMessage());

            return array('code' => 400, 'message' => '发生异常');
        }

        return array('code' => 200, 'message' => 'success', 'data' => $rows);
    }

    /**
     * 转接分配数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getTranCallListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $callId = empty($param['call_id']) ? '' : trim($param['call_id']);

        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        if (empty($callId)) {
            return array('code' => 403, 'message' => 'call_id不能为空');
        }

        $callId = (int) $callId;
        $type = "win_queue_cdr";
        $condition = array(
            'fixup' => array(
                'index' => 'queue_cdr',
                'type' => $type,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => $vccId)),
        );
        $condition['term']['call_id'] = array('type' => 'match', 'value' => $callId);
        $rows = $this->getDataFromESAndMongo($condition, 'win_queue_cdr', array('field' => 'end_time', 'order' => 'desc'));
        $data = $rows['data'];

        $translator = $this->container->get('translator');
        /** @var array $transCall 转接分配接结果 */
        $transCall = $this->container->getParameter('TRANSCALL');
        /** @var array $queueEndResult 队列挂断类型 */
        $queueEndResult = $this->container->getParameter('QENDRESULT');

        $result = array();
        foreach ($data as $k => $v) {
            $result[$k]['call_id'] = (string) $v['call_id'];
            $result[$k]['que_name'] = $v['que_name'];
            $result[$k]['ag_num'] = $v['ag_num'].' '.$v['ag_name'];
            $result[$k]['ag_phone'] = $v['ag_phone'];
            $result[$k]['ent_que_time'] = $v['ent_que_time'] ? date("Y-m-d H:i:s", $v['ent_que_time']) : "";
            $result[$k]['assign_time'] = $v['assign_time'] ? date("Y-m-d H:i:s", $v['assign_time']) : "";
            $result[$k]['link_time'] = $v['link_time'] ? date("Y-m-d H:i:s", $v['link_time']) : "";
            $result[$k]['end_time'] = $v['end_time'] ? date("Y-m-d H:i:s", $v['end_time']) : "";
            $result[$k]['que_secs'] = $v['que_secs'];
            $result[$k]['ring_secs'] = $v['ring_secs'];
            $result[$k]['conn_secs'] = $v['conn_secs'];
            $result[$k]['all_secs'] = $v['all_secs'];
            $result[$k]['result'] = isset($transCall[$v['result']]) ?
                $transCall[$v['result']] : $translator->trans('Is not set');
            $result[$k]['endresult'] = isset($queueEndResult[$v['endresult']]) ?
                $queueEndResult[$v['endresult']] : $translator->trans('Is not set');
        }

        // 去掉未转技能组数据
        if (count($result) == 1 && $result[0]['endresult'] == $queueEndResult['21']) {
            $result = array();
        }

        return array('code' => '200', 'message' => 'ok', 'data' => $result);
    }

    /**
     * 呼出明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getCalloutListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id';
        $condition['vcc_id'] = $vccId;
        $where .= ' AND call_type IN (1,3,5)';

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $where .= " AND ag_phone LIKE '%".$info['filter']['ag_phone']."%'";
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $where .= " AND cus_phone LIKE '%".$info['filter']['cus_phone']."%'";
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }

            if (isset($info['filter']['call_id'])) {
                $where .= " AND call_id = :call_id ";
                $condition['call_id'] = trim($info['filter']['call_id']);
            }

            if (isset($info['filter']['serv_num']) && !empty($info['filter']['serv_num'])) {
                $where .= " AND serv_num = :serv_num ";
                $condition['serv_num'] = trim($info['filter']['serv_num']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $adsDbal = $this->container->get('doctrine.dbal.ads_connection');
        /** @var  $count (总记录数) */
        $count = $adsDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE '.$where,
            $condition
        );
        $forPageParam = $this->pageForAdsSearch($param['forPageParam'], $info['pagination']['rows'], $count);
        $where .= $forPageParam['where'];
        $orderBy = $forPageParam['orderBy'];
        $sqlLimit = $forPageParam['sqlLimit'];
        $isReversed = $forPageParam['isReversed'];
        $isChangeRowlist = $forPageParam['isChangeRowlist'];
        if (empty($info['export'])) {
            $rows = $adsDbal->fetchAll(
                "SELECT id,ag_num,group_id,call_id,ag_name,serv_num,ag_phone,cus_phone,que_name,start_time,".
                "cus_phone_areacode,cus_phone_areaname, ".
                "end_time,ring_secs,conn_secs,all_secs,ag_phone_areacode,ag_phone_areaname,result".
                " FROM win_agcdr ".
                "WHERE ".$where.$orderBy.$sqlLimit,
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $adsDbal->fetchAll(
                "SELECT id,ag_num,group_id,ag_name,serv_num,ag_phone,cus_phone,que_name,start_time,".
                "cus_phone_areacode,cus_phone_areaname, ".
                "end_time,ring_secs,conn_secs,all_secs,ag_phone_areacode,ag_phone_areaname,result".
                " FROM win_agcdr ".
                "WHERE ".$where.$orderBy,
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $adsDbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_agcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT id,ag_num,group_id,ag_name,serv_num,ag_phone,cus_phone,que_name,start_time,".
                "cus_phone_areacode,cus_phone_areaname, ".
                "end_time,ring_secs,conn_secs,all_secs,ag_phone_areacode,ag_phone_areaname,`result`".
                " FROM win_agcdr AS wa INNER JOIN(".
                "SELECT id from win_agcdr ".
                "WHERE ".$where." AND id <= '".$maxId."'".$orderBy.' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }

        $gridData = array();
        if ($isReversed == true) {
            $rows = array_reverse($rows);
        }

        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $v['end_time']);
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $forPageParam['totalPage'],
            'data' => $gridData,
            'isChangeRowlist' => $isChangeRowlist
        );
    }

    /**
     * 从elasticsearch+mongodb中获取呼出明细数据
     *
     * @param array $param
     * @return array
     */
    public function getCalloutListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];

        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        //$field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];

        $type = "win_agcdr";
        $condition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );
        $condition['term']['call_type'] = array('type' => 'should', 'value' => '1,3,5');
        $startTime = isset($info['filter']['start_time']) ? $info['filter']['start_time'] : date('Y-m-d');
        $endTime = isset($info['filter']['end_time']) ? $info['filter']['end_time']: date('Y-m-d').' 23:59:59';
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($startTime, $endTime, 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => -1);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['ag_phone']);
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $condition['term']['cus_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['cus_phone']);
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => $info['filter']['group_id']);
            }

            if (isset($info['filter']['call_id'])) {
                $condition['term']['call_id'] = array('type' => 'match', 'value' => trim($info['filter']['call_id']));
            }

            if (isset($info['filter']['serv_num']) && !empty($info['filter']['serv_num'])) {
                $condition['term']['serv_num'] = array('type' => 'match', 'value' => trim($info['filter']['serv_num']));
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $condition['export'] = empty($info['export']) ? 0 : 1;
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }

        $rows = $this->getDataFromESAndMongo($condition, 'win_agcdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $item['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
            $gridData[$k]['cus_phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($item['cus_phone'], $cusPhoneAuthority);
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $totalPages,
            'data' => $gridData,
            'page' => $page,
        );
    }

    /**
     * 坐席通话明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getCallListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $condition = array('vcc_id' => $vccId);
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $where .= " AND ag_phone LIKE '%".$info['filter']['ag_phone']."%'";
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $where .= " AND cus_phone LIKE '%".$info['filter']['cus_phone']."%'";
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }

            if (isset($info['filter']['call_type']) && $info['filter']['call_type'] != '-1') {
                $where .= " AND call_type = :call_type";
                $condition['call_type'] = $info['filter']['call_type'];
            }

            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $where .= " AND endresult = :endresult";
                $condition['endresult'] = $info['filter']['endresult'];
            }

            if (isset($info['filter']['call_id']) && !empty($info['filter']['call_id'])) {
                $where .= " AND call_id LIKE '%".$info['filter']['call_id']."%'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $adsDbal = $this->container->get('doctrine.dbal.ads_connection');
        /** @var  $count (总记录数) */
        $count = $adsDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE '.$where,
            $condition
        );

        $forPageParam = $this->pageForAdsSearch($param['forPageParam'], $info['pagination']['rows'], $count);
        $where .= $forPageParam['where'];
        $orderBy = $forPageParam['orderBy'];
        $sqlLimit = $forPageParam['sqlLimit'];
        $isReversed = $forPageParam['isReversed'];
        $isChangeRowlist = $forPageParam['isChangeRowlist'];
        $totalPage = $forPageParam['totalPage'];

        if (empty($info['export'])) {
            $rows = $adsDbal->fetchAll(
                "SELECT id,ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,call_type,call_id,start_time, ".
                "cus_phone_areacode,cus_phone_areaname,".
                "end_time,conn_secs,ag_phone_areacode,ag_phone_areaname,result,endresult".
                " FROM win_agcdr ".
                "WHERE ".$where.$orderBy.$sqlLimit,
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $adsDbal->fetchAll(
                "SELECT id,ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,call_type,call_id,start_time, ".
                "cus_phone_areacode,cus_phone_areaname,".
                "end_time,conn_secs,ag_phone_areacode,ag_phone_areaname,result,endresult".
                " FROM win_agcdr ".
                "WHERE ".$where.$orderBy,
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_agcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT id,ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,call_type,call_id,start_time, ".
                "cus_phone_areacode,cus_phone_areaname,".
                "end_time,conn_secs,ag_phone_areacode,ag_phone_areaname,`result`,endresult".
                " FROM win_agcdr AS wa INNER JOIN(".
                "SELECT id from win_agcdr ".
                " WHERE ".$where." AND id <= '".$maxId."'".$orderBy.' %LIMIT%) AS waa USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }

        $gridData = array();
        /** @var array $callType 呼叫类型 */
        $callType = $this->container->getParameter('CALLTYPE');
        /** @var array $endReason 结束类型 */
        $endReason = $this->container->getParameter('ENDREASON');

        if ($isReversed == true) {
            $rows = array_reverse($rows);
        }

        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $v['end_time']);
            $gridData[$k]['call_type'] = isset($callType[$v['call_type']]) ? $callType[$v['call_type']] : '';
            $gridData[$k]['endresult'] = isset($endReason[$v['endresult']]) ? $endReason[$v['endresult']] : '其他';
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $totalPage,
            'data' => $gridData,
            'isChangeRowlist' => $isChangeRowlist
        );
    }

    /**
     * 从elassearch中获取坐席通话明细数据
     *
     * @param array $param
     * @return array
     */
    public function getCallListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = isset($param['big']) ? $param['big'] : false;
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        $field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];

        $type = "win_agcdr";
        $condition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => $field,
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );
        $startTime = isset($info['filter']['start_time']) ? $info['filter']['start_time'] : date('Y-m-d');
        $endTime = isset($info['filter']['end_time']) ? $info['filter']['end_time'] : date('Y-m-d').' 23:59:59';
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($startTime, $endTime, 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => -1);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }
            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $endResult = (int) $info['filter']['endresult'];
                if ($info['filter']['endresult'] == '11' || $info['filter']['endresult'] == '12') {
                    $condition['term']['endresult'] = array('type' => 'match', 'value' => $endResult);
                } else {
                    $condition['term']['endresult'] = array('type' => 'multiNomatch', 'value' => array('11', '12'), 'multi' => true);
                }
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['ag_phone']);
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $condition['term']['cus_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['cus_phone']);
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }

            if (isset($info['filter']['call_type']) && $info['filter']['call_type'] != '-1') {
                if ($info['filter']['call_type'] == 2) {
                    //11转内线 12转电话
                    $condition['term']['call_type'] = array('type' => 'should', 'value' => '2,11,12');
                } else {
                    $condition['term']['call_type'] = array('type' => 'match', 'value' => $info['filter']['call_type']);
                }
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (isset($info['filter']['call_id']) && !empty($info['filter']['call_id'])) {
                $callId = (int) $info['filter']['call_id'];
                $condition['term']['call_id'] = array('type' => 'match', 'value' => $callId);
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => (int) $info['filter']['group_id']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $condition['export'] = empty($info['export']) ? 0 : 1;
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }

        $rows = $this->getDataFromESAndMongo($condition, 'win_agcdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        /** @var array $callType 呼叫类型 */
        $callType = $this->container->getParameter('CALLTYPE');
        $callType[11] = '呼入';
        $callType[12] = '呼入';
        /** @var array $endReason 结束类型 */
        $endReason = $this->container->getParameter('ENDREASON');
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['call_id'] = (string) $item['call_id'];
            $gridData[$k]['ag_phone'] = (string) $item['ag_phone'];
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $item['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
            $gridData[$k]['call_type'] = isset($callType[$item['call_type']]) ? $callType[$item['call_type']] : '';
            $gridData[$k]['endresult'] = isset($endReason[$item['endresult']]) ? $endReason[$item['endresult']] : '其他';
            $gridData[$k]['cus_phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($item['cus_phone'], $cusPhoneAuthority);
        }
        unset($resultData);
        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $totalPages,
            'data' => $gridData,
            'page' => $page
        );
    }

    /**
     * 坐席操作明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentStateListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }
        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['vcc_id'] = -1;
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['ag_sta_type']) && $info['filter']['ag_sta_type'] != '-1') {
                $where .= " AND ag_sta_type = :ag_sta_type";
                $condition['ag_sta_type'] = $info['filter']['ag_sta_type'];
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agsta_detail '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_agsta_detail', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,ag_sta_reason,ag_login_ip,start_time,duration,bend".
                " FROM win_agsta_detail ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,ag_sta_reason,ag_login_ip,start_time,duration,bend".
                " FROM win_agsta_detail ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            //获取所有自定义的操作
            $agStaReason = $this->getAgentStaReason($vccId);
            $keys = '';
            foreach($agStaReason as $key=>$val) {
                $keys .= $key.',';
            }
            $keys .= "0,1,11,''" ;
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_agsta_detail '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,if(if(ag_sta_type=1,'',ag_sta_reason)in(".$keys."),if(ag_sta_type=1,'',ag_sta_reason),-1)as ag_sta_reason,ag_login_ip,start_time,duration,bend,if(bend=1,(start_time+duration),'')as end_time".
                " FROM win_agsta_detail AS wad INNER JOIN(".
                " SELECT id from win_agsta_detail".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwad USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        $agStaReason = $this->getAgentStaReason($vccId);
        /** @var array $staReason 操作原因 */
        $staReason = $this->container->getParameter('STAREASON');
        /** @var array $operationStaus 操作状态 */
        $operationStaus = $this->container->getParameter('OPERATIONSTATUS');

        foreach ($rows as $k => $v) {
            /** 计算结束时间 */
            if ($v['bend']) {
                $endTime = $v['start_time'] + $v['duration'];
            } else {
                $endTime = 0;
            }

            $gridData[$k] = $v;
            //$gridData[$k]['ag_num'] = $v['ag_num']." ".$v['ag_name'];
            if (empty($v['ag_login_ip'])) {
                if($v['ag_sta_type'] == 1 && $v['ag_sta_reason'] == 3) {
                    $gridData[$k]['ag_login_ip'] = "静态坐席";
                } else {
                    $gridData[$k]['ag_login_ip'] = "";
                }
            } else {
                $gridData[$k]['ag_login_ip'] = $v['ag_login_ip'];
            }
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['end_time'] = empty($endTime) ? "" : date("Y-m-d H:i:s", $endTime);
            $gridData[$k]['duration'] = $this->container->get("icsoc_core.common.class")->formateTime($v['duration']);
            $gridData[$k]['bend'] = $v['bend'] ? '结束' : '';
            if ($v['ag_sta_type'] == 2) {
                if ($v['ag_sta_reason'] == 1) {
                    $gridData[$k]['ag_sta_reason'] = isset($staReason[$v['ag_sta_reason']]) ?
                        $staReason[$v['ag_sta_reason']] : '';
                } else {
                    $gridData[$k]['ag_sta_reason'] = isset($agStaReason[$v['ag_sta_reason']])
                        ? $agStaReason[$v['ag_sta_reason']] : '坐席主动示忙';
                }
            } else {
                $gridData[$k]['ag_sta_reason'] = '';
            }
            $gridData[$k]['ag_sta_type'] = isset($operationStaus[$v['ag_sta_type']]) ?
                $operationStaus[$v['ag_sta_type']] : "";
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 技能组通话明细
     *
     * @param array $param
     *
     * @return array
     */
    public function getQueueListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }
        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }

                $where .= "AND ent_que_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND ent_que_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND endresult = :result";
                $condition['result'] = $info['filter']['result'];
            }

            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $where .= " AND ag_phone LIKE '%".$info['filter']['called']."%'";
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num= :server_num";
                $condition['server_num'] = $info['filter']['server_num'];
            }

        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_queue_cdr '.
            'WHERE bend=1 AND '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_queue_cdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_name,ag_num,call_id,server_num,caller_num,que_name,all_secs,conn_secs,ring_secs,que_secs, ".
                " endresult,link_time,end_time,ag_phone_areacode,ag_phone_areaname,caller_areacode,caller_areaname,".
                " ent_que_time start_time, assign_time".
                " FROM win_queue_cdr ".
                "WHERE bend=1 AND ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_name,ag_num,call_id,server_num,caller_num,que_name,all_secs,conn_secs,ring_secs,que_secs, ".
                " endresult,link_time,end_time,ag_phone_areacode,ag_phone_areaname,caller_areacode,caller_areaname,".
                " ent_que_time start_time, assign_time".
                " FROM win_queue_cdr ".
                "WHERE bend=1 AND ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_queue_cdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_name,ag_num,call_id,server_num,caller_num,que_name,all_secs,conn_secs,ring_secs,que_secs, ".
                " endresult,link_time,end_time,ag_phone_areacode,ag_phone_areaname,caller_areacode,caller_areaname,".
                " ent_que_time start_time, assign_time".
                " FROM win_queue_cdr AS wq INNER JOIN(".
                "SELECT id from win_queue_cdr".
                " WHERE bend=1 AND ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }

        $gridData = array();
        $endResults = $this->container->getParameter('END RESULT');

        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date('Y-m-d H:i:s', $v['start_time']);
            $gridData[$k]['end_time'] = date('Y-m-d H:i:s', $v['end_time']);
            $gridData[$k]['link_time'] = empty($v['link_time']) ? '未接通' : date('Y-m-d H:i:s', $v['link_time']);
            $gridData[$k]['assign_time'] = empty($v['assign_time']) ? '未分配' : date('Y-m-d H:i:s', $v['assign_time']);
            $gridData[$k]['endresult'] = (!array_key_exists($v['endresult'], $endResults)) ? '未接通' :
                $endResults[$v['endresult']];
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 从elasticsearch中获取技能组通话明细数据
     *
     * @param array $param
     * @return array
     */
    public function getQueueListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        $field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];
        $type = "win_queue_cdr";
        $condition = array(
            'fixup' => array(
                'index' => 'queue_cdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => $field,
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => -1);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'ent_que_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'ent_que_time', 'operation' => 'lte');
            }
            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $condition['term']['endresult'] = array('type' => 'match', 'value' => $info['filter']['result']);
            } else {
                $condition['term']['endresult'] = array('type' => 'nomatch', 'value' => 21);//过滤掉21;
            }

            //坐席号码
            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['called']);
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'match', 'value' => $info['filter']['server_num']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && !isset($info['filter']['group_id'])) {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }
        $condition['export'] = empty($info['export']) ? 0 : 1;
        $rows = $this->getDataFromESAndMongo($condition, 'win_queue_cdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        $endResults = $this->container->getParameter('END RESULT');
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['ent_que_time'] = date('Y-m-d H:i:s', $item['ent_que_time']);
            $gridData[$k]['end_time'] = date('Y-m-d H:i:s', $item['end_time']);
            $gridData[$k]['link_time'] = empty($item['link_time']) ? '未接通' : date('Y-m-d H:i:s', $item['link_time']);
            $gridData[$k]['assign_time'] = empty($item['assign_time']) ? '未分配' : date('Y-m-d H:i:s', $item['assign_time']);
            $gridData[$k]['endresult'] = (!array_key_exists($item['endresult'], $endResults)) ? '未接通' :
                $endResults[$item['endresult']];
            $gridData[$k]['caller_num'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($item['caller_num'], $cusPhoneAuthority);
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 获取漏话sql
     *
     * @param  array $param
     *
     * @return string $sql
     */
    public function getLostListSql(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);

        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = " vcc_id = $vccId ";
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $startDate = strtotime($startDate);
                $where .= "AND start_time >= $startDate ";
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $endDate = strtotime($endDate);
                $where .= "AND start_time <= $endDate ";
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $where .= " AND caller LIKE '%".$info['filter']['caller']."%'";
            }
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num LIKE '%".$info['filter']['server_num']."%'";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $count = $this->dbal->fetchColumn('SELECT count(*) '.
            'FROM win_lost_cdr '.
            'WHERE '.$where);
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_lost_cdr', $info);

        $sql = "SELECT caller,caller_areacode,caller_areaname,que_name,server_num,start_time,reason ".
            "FROM win_lost_cdr ".
            "WHERE ".$where.
            ' ORDER BY '.$page['sort'];

        return $sql;
    }

    /**
     * 漏话明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getLostListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;
        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $where .= " AND caller LIKE '%".$info['filter']['caller']."%'";
            }
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num LIKE '%".$info['filter']['server_num']."%'";
            }
            if (isset($info['filter']['reason']) && $info['filter']['reason'] != -1) {
                switch ($info['filter']['reason']) {
                    case 1:
                        $where .= ' AND reason in (1,101)';
                        break;
                    case 2:
                        $where .= ' AND reason in (2,102)';
                        break;
                    case 3:
                        $where .= ' AND reason in (3,103)';
                        break;
                    case 4:
                        $where .= ' AND reason in (4,104)';
                        break;
                    case 5:
                        $where .= ' AND reason in (5,105)';
                        break;
                    case 6:
                        $where .= ' AND reason in (6,106)';
                        break;
                    case 7:
                        $where .= ' AND reason in (7,107)';
                        break;
                    case 8:
                        $where .= ' AND reason in (8,108)';
                        break;
                    case 109:
                        $where .= ' AND reason = 109';
                        break;
                }
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_lost_cdr '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_lost_cdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT caller,que_name,server_num,start_time,caller_areacode,caller_areaname,reason ".
                "FROM win_lost_cdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT caller,que_name,server_num,start_time,caller_areacode,caller_areaname,reason ".
                "FROM win_lost_cdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_lost_cdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT caller,que_name,server_num,start_time,caller_areacode,caller_areaname,reason ".
                "FROM win_lost_cdr AS wlc INNER JOIN(".
                " SELECT id from win_lost_cdr ".
                "WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        /** @var array $lostReason 漏话原因 */
        $lostReason = $this->container->getParameter('LOSTREASON');
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            if ($v['reason'] == 1 || $v['reason'] == 101) {
                $gridData[$k]['reason'] = $lostReason[1];
            } else if ($v['reason'] == 2 || $v['reason'] == 102) {
                $gridData[$k]['reason'] = $lostReason[2];
            } else if ($v['reason'] == 3 || $v['reason'] == 103) {
                $gridData[$k]['reason'] = $lostReason[3];
            } else if ($v['reason'] == 4 || $v['reason'] == 104) {
                $gridData[$k]['reason'] = $lostReason[4];
            } else if ($v['reason'] == 5 || $v['reason'] == 105) {
                $gridData[$k]['reason'] = $lostReason[5];
            } else if ($v['reason'] == 6 || $v['reason'] == 106){
                $gridData[$k]['reason'] = $lostReason[6];
            } else if ($v['reason'] == 7 || $v['reason'] == 107) {
                $gridData[$k]['reason'] = $lostReason[7];
            } else if ($v['reason'] == 8 || $v['reason'] == 108) {
                $gridData[$k]['reason'] = $lostReason[8];
            } else if ($v['reason'] == 109) {
                $gridData[$k]['reason'] = $lostReason[109];
            } else {
                $gridData[$k]['reason'] = "";
            }
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * (mongodb)漏话明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getLostListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        //$field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];

        $condition['term']['vcc_id'] = $vccId;
        $type = "win_incdr";
        $condition = array(
            'fixup' => array(
                'index' => 'incdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
                'result' => array('type' => 'nomatch', 'value' => (int) 0),
            ),
        );

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }
            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $condition['term']['caller'] = array('type' => 'wildcard', 'value' => $info['filter']['caller']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'wildcard', 'value' => $info['filter']['server_num']);
            }
            if (isset($info['filter']['reason']) && $info['filter']['reason'] != -1) {
                $condition['term']['result'] = array('type' => 'match', 'value' => (int) $info['filter']['reason']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id'])) {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id'])) {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id'])) {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $condition['export'] = empty($info['export']) ? 0 : 1;
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }
        $rows = $this->getDataFromESAndMongo($condition, 'win_incdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        /** @var array $lostReason 漏话原因 */
        $lostReason = $this->container->getParameter('INRESULT');
        foreach ($resultData as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['result'] = isset($lostReason[$v['result']]) ? $lostReason[$v['result']] : '';
            $gridData[$k]['caller'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($v['caller'], $cusPhoneAuthority);
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 咨询三方数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getConferenceListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id';
        $condition['vcc_id'] = $vccId;
        $where .= ' AND ext_type = 1 ';
        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_ernum']) && !empty($info['filter']['ag_ernum'])) {
                $where .= " AND (ag_ernum LIKE '%".$info['filter']['ag_ernum']."%' OR ".
                    "ag_ername LIKE '%".$info['filter']['ag_ernum']."%')";
            }

            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $where .= " AND endresult = :endresult";
                $condition['endresult'] = $info['filter']['endresult'];
            }

            if (isset($info['filter']['call_phone']) && !empty($info['filter']['call_phone'])) {
                $where .= " AND call_phone LIKE '%".$info['filter']['call_phone']."%'";
            }

            if (isset($info['filter']['ext_phone']) && !empty($info['filter']['ext_phone'])) {
                $where .= " AND ext_phone LIKE '%".$info['filter']['ext_phone']."%'";
            }

            if (isset($info['filter']['max_secs']) && !empty($info['filter']['max_secs'])) {
                $where .= " AND conn_secs >='".$info['filter']['max_secs']."'";
            }

            if (isset($info['filter']['min_secs']) && !empty($info['filter']['min_secs'])) {
                $where .= " AND conn_secs <='".$info['filter']['min_secs']."'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_er', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_extcdr '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_extcdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_extcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr AS wq INNER JOIN(".
                " SELECT id from win_extcdr ".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }

        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['conn2_time'] = $v['conn2_time'] ? date("Y-m-d H:i:s", $v['conn2_time']) : "";
            $gridData[$k]['conn_time'] = $v['conn_time'] ? date("Y-m-d H:i:s", $v['conn_time']) : "";
            $gridData[$k]['ext_phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($v['ext_phone'], $cusPhoneAuthority);
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 监听强插数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getMonitorListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id';
        $condition['vcc_id'] = $vccId;
        $where .= ' AND ext_type = 2 ';

        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_ernum']) && !empty($info['filter']['ag_ernum'])) {
                $where .= " AND (ag_ernum LIKE '%".$info['filter']['ag_ernum']."%' OR ".
                    "ag_ername LIKE '%".$info['filter']['ag_ernum']."%')";
            }

            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $where .= " AND endresult = :endresult";
                $condition['endresult'] = $info['filter']['endresult'];
            }

            if (isset($info['filter']['call_phone']) && !empty($info['filter']['call_phone'])) {
                $where .= " AND call_phone LIKE '%".$info['filter']['call_phone']."%'";
            }

            if (isset($info['filter']['ext_phone']) && !empty($info['filter']['ext_phone'])) {
                $where .= " AND ext_phone LIKE '%".$info['filter']['ext_phone']."%'";
            }

            if (isset($info['filter']['max_secs']) && !empty($info['filter']['max_secs'])) {
                $where .= " AND conn_secs >='".$info['filter']['max_secs']."'";
            }

            if (isset($info['filter']['min_secs']) && !empty($info['filter']['min_secs'])) {
                $where .= " AND conn_secs <='".$info['filter']['min_secs']."'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_er', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_extcdr '.
            'WHERE '.$where,
            $condition
        );

        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_extcdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_extcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr AS wq INNER JOIN(".
                " SELECT id from win_extcdr".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['conn2_time'] = $v['conn2_time'] ? date("Y-m-d H:i:s", $v['conn2_time']) : "";
            $gridData[$k]['call_phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($v['call_phone'], $cusPhoneAuthority);
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 技能组来电分配数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getInallotListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $where = 'vcc_id = :vcc_id AND que_id != 0 ';
        $condition = array('vcc_id' => $vccId);

        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['vcc_id'] = -1;
        }
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        /** @var  $count (总记录数) */
        $count = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_incdr '.
            'WHERE '.$where,
            $condition
        );

        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_incdr', $info);
        $rows = $this->cdrDbal->fetchAll(
            "SELECT que_name,count(*) AS num ".
            "FROM win_incdr ".
            "WHERE ".$where.
            " GROUP BY que_id".
            ' ORDER BY '.$page['sort'],
            $condition
        );

        $data = array();

        foreach ($rows as $k => $v) {
            $data[$k]['que_name'] = $v['que_name'];
            $data[$k]['num'] = $v['num'];
            $data[$k]['rate'] = empty($count) ? '0.00%' : round($v['num'] / $count * 100, 2).'%';
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($data),
            'data' => $data,
        );
    }

    /**
     * 技能组转移明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getqueueTransCallListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;
        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

//            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
//                $where .= " AND result = :result";
//                $condition['result'] = $info['filter']['result'];
//            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $where .= " AND ag_phone LIKE '%".$info['filter']['ag_phone']."%'";
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $where .= " AND cus_phone LIKE '%".$info['filter']['cus_phone']."%'";
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $where .= " AND que_id = :que_id ";
                $condition['que_id'] = $info['filter']['que_id'];
            }
            //转移技能组id
            if (isset($info['filter']['que_id_trans']) && $info['filter']['que_id_trans'] != '-1') {
                $where .= " AND que_id_trans = '".$info['filter']['que_id_trans']."'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_trans_call '.
            'WHERE '.$where,
            $condition
        );

        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_trans_call', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,start_time,que_name_trans ".
                "FROM win_trans_call ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,start_time,que_name_trans ".
                "FROM win_trans_call ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_trans_call '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_num,group_id,ag_name,ag_phone,cus_phone,que_name,start_time,que_name_trans ".
                "FROM win_trans_call AS wtc INNER JOIN(".
                " SELECT id from win_trans_call ".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            //$gridData[$k]['ag_num'] = $v['ag_num']." ".$v['ag_name'];
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['cus_phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($v['cus_phone'], $cusPhoneAuthority);
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }


    /**
     * 获取坐席状态原因
     *
     * @param $vccId
     *
     * @return array
     */
    public function getAgentStaReason($vccId)
    {
        if (empty($vccId)) {
            return array(
                'code' => 403,
                'message' => '未定义的企业',
            );
        }

        $result = $this->dbal->fetchAll(
            "SELECT id,stat_reason ".
            "FROM win_agstat_reason ".
            "WHERE vcc_id=:vcc_id",
            array(':vcc_id' => $vccId)
        );

        $AgentStaReason = array();

        foreach ($result as $row) {
            $AgentStaReason[$row['id']] = $row['stat_reason'];
        }

        return $AgentStaReason;
    }


    /**
     * 获取坐席状态明细
     *
     * @param $param
     *
     * @return array
     */
    public function getAgentStaDetail($param)
    {
        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业',
            );
        }

        $type = isset($param['type']) ? $param['type'] : 'day';
        $reason = isset($param['reason']) ? $param['reason'] : array();
        $data = array();
        switch ($type) {
            case 'day':
                $AgentStaDetail = $this->cdrDbal->fetchAll(
                    "SELECT count(id) AS num,sum(duration) AS duration,ag_id,ag_sta_type,ag_sta_reason,".
                    "concat(start_year,'-',start_month,'-',start_day) AS start_date ".
                    "FROM win_agsta_detail ".
                    "WHERE vcc_id = ".$param['vcc_id']." AND ag_sta_type=2 ".$param['condition']." ".
                    "GROUP BY ag_sta_reason,concat(start_year,'-',start_month,'-',start_day),ag_id"
                );

                foreach ($AgentStaDetail as $row) {
                    // 配置的置忙原因
                    if (array_key_exists($row['ag_sta_reason'], $reason)) {
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] += $row['duration'];
                    } else {
                        // 其它置忙
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['num'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['secs'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']]['other']['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']]['other']['secs'] += $row['duration'];
                    }
                }
                break;
            case 'month':
                $AgentStaDetail = $this->cdrDbal->fetchAll(
                    "SELECT count(id) AS num,sum(duration) AS duration,ag_id,ag_sta_reason,".
                    "concat(start_year,'-',start_month) AS start_date ".
                    "FROM win_agsta_detail ".
                    "WHERE vcc_id = ".$param['vcc_id']." AND ag_sta_type=2 ".$param['condition']." ".
                    "GROUP BY ag_sta_reason,concat(start_year,'-',start_month,'-'),ag_id"
                );

                foreach ($AgentStaDetail as $row) {
                    // 配置的置忙原因
                    if (array_key_exists($row['ag_sta_reason'], $reason)) {
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] += $row['duration'];
                    } else {
                        // 其它置忙
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['num'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['secs'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']]['other']['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']]['other']['secs'] += $row['duration'];
                    }
                }
                break;
            case 'hour':
                $AgentStaDetail = $this->cdrDbal->fetchAll(
                    "SELECT count(id) AS num,sum(duration) AS duration,ag_id,ag_sta_reason,".
                    "concat(start_year,'-',start_month,'-',start_day,' ',start_hour) AS start_date ".
                    "FROM win_agsta_detail ".
                    "WHERE vcc_id = ".$param['vcc_id']." AND ag_sta_type=2 ".$param['condition']." ".
                    "GROUP BY ag_sta_reason,start_year,start_month,start_day,start_hour,ag_id"
                );

                foreach ($AgentStaDetail as $row) {
                    // 配置的置忙原因
                    if (array_key_exists($row['ag_sta_reason'], $reason)) {
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'])) {
                            $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']][$row['ag_sta_reason']]['secs'] += $row['duration'];
                    } else {
                        // 其它置忙
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['num'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['num'] = 0;
                        }
                        if (!isset($data[$row['start_date']][$row['ag_id']]['other']['secs'])) {
                            $data[$row['start_date']][$row['ag_id']]['other']['secs'] = 0;
                        }

                        $data[$row['start_date']][$row['ag_id']]['other']['num'] += $row['num'];
                        $data[$row['start_date']][$row['ag_id']]['other']['secs'] += $row['duration'];
                    }
                }
                break;
            default:
                break;
        }

        return $data;
    }

    /**
     * 获取业务组状态明细
     *
     * @param $param
     *
     * @return array
     */
    public function getGroupStaDetail($param)
    {
        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业',
            );
        }

        $AgentStaDetail = $this->cdrDbal->fetchAll(
            "SELECT count(id) AS num,sum(duration) AS duration,group_id,ag_sta_type,ag_sta_reason,".
            "start_year,start_month,start_day,start_hour ".
            "FROM win_agsta_detail ".
            "WHERE vcc_id = ".$param['vcc_id']." ".$param['condition']." ".
            "GROUP BY ag_sta_type,ag_sta_reason,start_year,start_month,start_day,start_hour,group_id"
        );

        return $AgentStaDetail;
    }

    /**
     * 满意度评价列表数据
     *
     * @param $param
     *
     * @return array
     */
    public function getEvaluateListData($param)
    {
        if (empty($param)) {
            return array(
                'code' => 401,
                'message' => '统计参数为空',
            );
        }

        if (!is_array($param)) {
            return array(
                'code' => 402,
                'message' => '统计参数格式不为数组',
            );
        }

        if (empty($param['vcc_id'])) {
            return array(
                'code' => 403,
                'message' => '未定义的企业',
            );
        }

        $where = ' vcc_id = '.$param['vcc_id'].' AND result = 0';

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $info = $param['info'];

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['evaluate']) && $info['filter']['evaluate'] != '-5') {
                $where .= " AND evaluate = '".$info['filter']['evaluate']."'";
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $where .= " AND ag_phone LIKE '%".$info['filter']['ag_phone']."%'";
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $where .= " AND cus_phone LIKE '%".$info['filter']['cus_phone']."%'";
            }
        }

        /** @var  $count (总记录数) */
        $count = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE '.$where
        );

        $limit = isset($info['pagination']['rows']) ? $info['pagination']['rows'] : 1000;

        $total_pages = ceil($count / $limit);
        $page = isset($info['pagination']['page']) && $info['pagination']['page'] > 1 ? $info['pagination']['page'] : 1;
        $page = $page > $total_pages ? $total_pages : $page;
        $start = $limit * $page - $limit;
        $start = $start > 0 ? $start : 0;

        $sort = isset($info['sort']['field']) ? $info['sort']['field'] : 'id';
        $order = isset($info['sort']['order']) && in_array(strtolower($info['sort']['order']), array('desc', 'asc'))
            ? $info['sort']['order'] : 'desc';

        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->dbal->fetchAll(
                "SELECT ag_id,ag_num,ag_name,ag_phone,cus_phone,que_name,call_type,call_id,start_time,".
                "end_time,conn_secs,result,endresult,evaluate,call_id,serv_num".
                " FROM win_agcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$sort.' '.$order.' LIMIT '.$start.','.$limit
            );
        } else {
            $rows = $this->dbal->fetchAll(
                "SELECT ag_id,ag_num,ag_name,ag_phone,cus_phone,que_name,call_type,call_id,start_time,".
                "end_time,conn_secs,result,endresult,evaluate,call_id,serv_num".
                " FROM win_agcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$sort.' '.$order
            );
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $total_pages,
            'data' => $rows,
        );
    }

    /**
     * 满意度评价汇总数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getEvaluateCollectListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        // $condition = array('vcc_id' => $vccId);
        $esCondition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => "win_agcdr",
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
                'result' => array('type' => 'match', 'value' => (int) 0)
            ),
        );
        //$where = 'vcc_id = :vcc_id AND ag_id != 0 AND result=0 ';
        //$condition = array('vcc_id' => $vccId);
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            //$condition['vcc_id'] = -1;
            $esCondition['term']['vcc_id'] = array('type' => 'match', 'value' => 0);
        }

        if (isset($info['filter'])) {
            /*if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }*/
            //坐席名称
            if (isset($info['filter']['ag_id']) && $info['filter']['ag_id'] != -1) {
                $esCondition['term']['ag_id'] = array('type' => 'match', 'value' => (int) $info['filter']['ag_id']);
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                /*$where .= " AND que_id = :que_id";
                $condition['que_id'] = $info['filter']['que_id'];*/
                $esCondition['term']['que_id'] = array('type' => 'match', 'value' => (int) $info['filter']['que_id']);
            }

            if (isset($info['filter']['call_type']) && $info['filter']['call_type'] != '-1') {
                $call_type = (int) $info['filter']['call_type'];
                if ($call_type == 1) {
                    //$where .= " AND (call_type = '1' OR call_type = '3' OR call_type = '5') ";
                    $esCondition['term']['call_type'] = array('type' => 'should', 'value' => '1,3,5');
                } elseif ($call_type == 2) {
                    //$where .= " AND (call_type = '2' OR call_type = '4' OR call_type = '6') ";
                    $esCondition['term']['call_type'] = array('type' => 'should', 'value' => '2,4,6');
                }
            }

            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                /*$where .= " AND start_time >= :start_time";
                $condition['start_time'] = strtotime($startDate);*/
                $esCondition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }

            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                /*$where .= " AND start_time <= :end_time";
                $condition['end_time'] = strtotime($endDate);*/
                $esCondition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                /*$where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];*/
                $esCondition['term']['group_id'] = array('type' => 'match', 'value' => (int) $info['filter']['group_id']);
            }
        }
        $esCondition['aggs']['group_by_ag_id'] = array(
            'terms' => array('field' => 'ag_id', 'size' => (int) 100000),
            'aggs' => array('group_by_evaluate' => array('terms' => array('field' => 'evaluate',  'size' => (int) 100000)))
        );
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_id'] == '-1') {
                $esCondition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $esCondition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $esCondition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        //   ? $info['sort']['order'] : 'desc';
        $arrCount = $this->container->get('icsoc_data.model.record')->getEsCount($esCondition);
        //print_r($arrCount['data']['aggregations']['group_by_ag_id']['buckets']);
        //获取所有的坐席
        $agData = $this->container->get('doctrine.dbal.default_connection')->fetchAll(
            "SELECT id,ag_name,ag_num FROM win_agent WHERE is_del=0 AND vcc_id=:vcc_id",
            array(':vcc_id' => $vccId)
        );
        $math = 0;
        $resultData = array();
        if (!empty($arrCount['data']['aggregations']['group_by_ag_id']['buckets'])) {
            foreach ($arrCount['data']['aggregations']['group_by_ag_id']['buckets'] as $key => $val) {
                foreach ($agData as $ke => $va) {
                    if ($val['key'] == $va['id']) {
                        foreach ($val['group_by_evaluate']['buckets'] as $k => $v) {
                            $resultData[$math]['ag_id'] = $val['key'];
                            $resultData[$math]['ag_name'] = $va['ag_name'];
                            $resultData[$math]['ag_num'] = $va['ag_num'];
                            $resultData[$math]['evaluate'] = $v['key'];
                            $resultData[$math]['num'] = $v['doc_count'];
                            $math++;
                        }
                    }
                }
            }
        }
        /*$resultData = $this->container->get('doctrine.dbal.ads_connection')->fetchAll(
            "SELECT  ag_id,ag_num,evaluate,ag_name,count(*) as num FROM win_agcdr
              WHERE $where GROUP BY ag_id,evaluate,ag_num,ag_name ",
            $condition
        );*/
        $result = $agids = array();
        foreach ($resultData as $data) {
            $key = sprintf('agId_%s', $data['ag_id']);
            if (in_array($data['evaluate'], array(-1,-2,-3,-4))) {
                $result[$key]['defeat'] = isset($result[$key]['defeat']) ? $result[$key]['defeat']+$data['num'] : $data['num'];
            }
            if (in_array($data['evaluate'], array(0,1,2,3,4,5,6,7,8,9))) {
                $result[$key]['success'] = isset($result[$key]['success']) ? $result[$key]['success']+$data['num'] : $data['num'];
            }

            $result[$key][sprintf('e%s', $data['evaluate'])] = $data['num'];
            $result[$key]['ag_name'] = $data['ag_name'];
            $result[$key]['ag_num'] = $data['ag_num'];
            $agids[] = $data['ag_id'];
        }
        $evaluate = array_keys($this->container->getParameter('EVALUATES'));
        $agids = array_unique($agids);
        foreach ($agids as $id) {
            $key = sprintf('agId_%s', $id);
            foreach ($evaluate as $eval) {
                if (!isset($result[$key][sprintf('e%s', $eval)])) {
                    $result[$key][sprintf('e%s',$eval)] = 0;
                }
            }
            $result[$key]['success'] = isset($result[$key]['success']) ? $result[$key]['success'] : 0;
            $result[$key]['defeat'] = isset($result[$key]['defeat']) ? $result[$key]['defeat'] : 0;
        }

//        $rows = $this->cdrDbal->fetchAll(
//            "SELECT ag_id,ag_num,ag_name,count(*) as num,".
//            "SUM(CASE WHEN evaluate IN (-1,-2,-3,-4) THEN 1 ELSE 0 END) AS defeat,".
//            "SUM(CASE WHEN evaluate IN (0,1,2,3,4,5,6,7,8,9) THEN 1 ELSE 0 END) AS success,".
//            "SUM(CASE WHEN evaluate = '-1' THEN 1 ELSE 0 END) AS 'e_1',".
//            "SUM(CASE WHEN evaluate = '-2' THEN 1 ELSE 0 END) AS 'e_2',".
//            "SUM(CASE WHEN evaluate = '-3' THEN 1 ELSE 0 END) AS 'e_3',".
//            "SUM(CASE WHEN evaluate = '-4' THEN 1 ELSE 0 END) AS 'e_4',".
//            "SUM(CASE WHEN evaluate = 0 THEN 1 ELSE 0 END) AS e0,".
//            "SUM(CASE WHEN evaluate = 1 THEN 1 ELSE 0 END) AS e1,".
//            "SUM(CASE WHEN evaluate = 2 THEN 1 ELSE 0 END) AS e2,".
//            "SUM(CASE WHEN evaluate = 3 THEN 1 ELSE 0 END) AS e3,".
//            "SUM(CASE WHEN evaluate = 4 THEN 1 ELSE 0 END) AS e4,".
//            "SUM(CASE WHEN evaluate = 5 THEN 1 ELSE 0 END) AS e5,".
//            "SUM(CASE WHEN evaluate = 6 THEN 1 ELSE 0 END) AS e6,".
//            "SUM(CASE WHEN evaluate = 7 THEN 1 ELSE 0 END) AS e7,".
//            "SUM(CASE WHEN evaluate = 8 THEN 1 ELSE 0 END) AS e8,".
//            "SUM(CASE WHEN evaluate = 9 THEN 1 ELSE 0 END) AS e9 ".
//            "FROM win_agcdr ".
//            "WHERE ".$where.
//            " GROUP BY ag_id ".
//            ' ORDER BY '.$sort.' '.$order,
//            $condition
//        );

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($result),
            'page' => 1,
            'total_pages' => 1,
            'data' => array_values($result),
        );
    }

    /**
     * 满意度评价汇总数据从mongodb获取数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getEvaluateCollectListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $condition = array('vcc_id' => (int)$vccId);

        $esCondition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => "win_agcdr",
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
                'result' => array('type' => 'match', 'value' => (int) 0)
            ),
        );
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition = array('vcc_id' => (int)0);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['date'] = array('$gte' => $startDate);
                $esCondition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['date'] = array_merge($condition['date'], array('$lte' => $endDate));
                $esCondition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate) + 86399, 'field' => 'start_time', 'operation' => 'lte');
            }
            //坐席名称
            if (isset($info['filter']['ag_id']) && $info['filter']['ag_id'] != -1) {
                $condition = array_merge($condition, array('ag_id' => (int)$info['filter']['ag_id']));
                $esCondition['term']['ag_id'] = array('type' => 'match', 'value' => (int) $info['filter']['ag_id']);
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $agId = array(-1);
            if (!empty($authority['ag_id']) && $info['filter']['ag_id'] == '-1') {
                if (is_array($authority['ag_id'])) {
                    foreach ($authority['ag_id'] as $v) {
                        $agId[] = (int)$v;
                    }
                }
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['ag_id'] == '-1') {
                //新的满意度汇总报表只有ag_id的搜索，所以要把技能组权限改成坐席的权限来控制
                $tmpRes = $this->dbal->fetchAll("select ag_id from win_agqu where que_id in (".implode(',', $authority['que_id']).")");
                $tmpIds = array();
                foreach ($tmpRes as $k => $v) {
                    $tmpIds[] = (int)$v['ag_id'];
                }
                $agId = array_merge($agId, $tmpIds);
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['ag_id'] == '-1') {
                $tmpRes = $this->dbal->fetchAll("select id from win_agent where group_id in (".implode(',', $authority['group_id']).") and is_del = 0");
                $tmpGroupIds = array();
                foreach ($tmpRes as $k => $v) {
                    $tmpGroupIds[] = (int)$v['id'];
                }
                $agId = array_merge($agId, $tmpGroupIds);
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        //从mongodb中，通过条件获取数据的值
        $document = $this->container->get('icsoc_core.mongodb_common.class')->otherGetDataForPage($condition, 'evalute');
        //默认值
        $hand['ag_num'] = '';
        $hand['evalute1'] = 0;
        $hand['evalute-1'] = 0;
        $hand['evalute-3'] = 0;
        $hand['evalute-4'] = 0;
        $hand['defeat'] = 0;
        $hand['ag_id'] = 0;
        $hand['transfer_rate'] = '0%';
        $footer['ag_num'] = '合计';
        $footer['evalute1'] = 0;
        $footer['evalute-1'] = 0;
        $footer['evalute-3'] = 0;
        $footer['evalute-4'] = 0;
        $footer['defeat'] = 0;
        $footer['ag_id'] = 0;
        //查询自定义的满意度 定义所有字段的默认值
        $evaluates = $this->container->get('icsoc_data.model.evaluate')->getEvaluateConfig(array('vccId' => $vccId));
        $otherData = json_decode($evaluates, true);
        $levelArr = array();        //按照不同等级 分组满意度评价
        //查出满意度配置 定义默认值
        if (!empty($otherData) && is_array($otherData)) {
            foreach ($otherData as $key => $val) {
                foreach ($val['keys'] as $ke => $va) {
                    $levelArr['one'][$key][] = $va['id'];
                    $hand = array_merge($hand, array($va['id'] => 0));
                    $hand = array_merge($hand, array($va['id'] . '_rate' => '0%'));
                    $footer = array_merge($footer, array($va['id'] => 0));
                    $footer = array_merge($footer, array($va['id'] . '_rate' => '0%'));
                    if (!empty($va['child']['keys']) && is_array($va['child']['keys'])) {
                        foreach ($va['child']['keys'] as $k => $v) {
                            $hand = array_merge($hand, array($v['id'] => 0));
                            $hand = array_merge($hand, array($v['id'] . '_rate' => '0%'));
                            $footer = array_merge($footer, array($v['id'] => 0));
                            $footer = array_merge($footer, array($v['id'] . '_rate' => '0%'));
                            $levelArr['two'][$key][] = $v['id'];
                        }
                    }
                }
            }
        }
        $result = array();
        $footerData = $footer;
        $agData = $this->container->get('doctrine.dbal.default_connection')->fetchAll(
            "SELECT id,ag_name,ag_num FROM win_agent WHERE is_del=0 AND vcc_id=:vcc_id",
            array(':vcc_id' => $vccId)
        );
        $esCondition['aggs'] = array(
            'group_by_ag_id'=>array(
                'terms'=>array(
                    'field'=>'ag_id',
                    'size' => 0,
                ),
            ),
        );
        $esData = $this->container->get('icsoc_data.model.record')->getEsCount($esCondition);
        $counts = 0;   //呼出接通量
        //$agIdStr = '';    //存放坐席ID
        foreach($document as $restaurant) {
            $restaurant = get_object_vars($restaurant);
            $haveing = false;
            $haveAgent = false;
            foreach ($agData as $val) {
                if ($restaurant['ag_id'] == $val['id']) {
                    $restaurant['ag_name'] = $val['ag_name'];
                    $restaurant['ag_num'] = $val['ag_num'];
                    $haveAgent = true;
                }
            }
            //没有坐席时跳出循环
            if (!$haveAgent) {
                continue;
            }
            //$agIdStr .= $restaurant['ag_id'].',';
            /* 未评论 */
            $restaurant['defeat'] = (empty($restaurant['evalute-1']) ? 0 : $restaurant['evalute-1'])
                + (empty($restaurant['evalute-3']) ? 0 : $restaurant['evalute-3'])
                + (empty($restaurant['evalute-4']) ? 0 : $restaurant['evalute-4']);
            foreach ($footer as $a => $b) {
                if (substr($a, -5) == '_rate') {
                    continue;
                }
                if ($a != '_id' && $a != 'ag_id' && $a != 'date' && $a != 'vcc_id' && $a != 'ag_name' && $a != 'ag_num') {
                    if (isset($footerData[$a])) {
                        $footerData[$a] += empty($restaurant[$a]) ? 0 : $restaurant[$a];
                    } else {
                        $footerData[$a] = empty($restaurant[$a]) ? $b : $restaurant[$a];
                    }
                }
            }
            if (!empty($result)) {
                foreach ($result as $k => $v) {
                    if ($v['ag_id'] == $restaurant['ag_id']) {
                        $haveing = true;
                        $sumArr = (array_merge_recursive($v,$restaurant));
                        foreach ($sumArr as $ke => $vl) {
                            if ($ke != '_id' && $ke != 'ag_id' && $ke != 'date' && $ke != 'vcc_id' && $ke != 'ag_name' && $ke != 'ag_num') {
                                if (is_array($vl)) {
                                    $result[$k][$ke] = $vl[0] + $vl[1];
                                } else {
                                    $result[$k][$ke] = $sumArr[$ke];
                                }
                            }
                        }
                        //计算转接率 和 参评率
                        foreach ($esData['data']['aggregations']['group_by_ag_id']['buckets'] as $esVal) {
                            if ($restaurant['ag_id'] == $esVal['key']) {
                                $counts += $count = $esVal['doc_count'];
                                //参评率   成功评价/呼入接通量
                                $result[$k]['evalute_rate'] = empty($count) ? '0%' : (round($result[$k]['evalute1']/$count, 4)*100).'%';
                                //转接率    成功评价+为评价挂机/呼入接通量
                                $transferRate = empty($count) ? 0 : (round(($result[$k]['evalute1'] + $result[$k]['evalute-3'])/$count, 4)*100);
                                if ($transferRate > 100) {
                                    $result[$k]['transfer_rate'] = '100%';
                                } else {
                                    $result[$k]['transfer_rate'] = $transferRate.'%';
                                }
                            }
                        }
                        //计算各种率
                        foreach ($levelArr as $leVal) {
                            foreach ($leVal as $keKe=>$leVl) {
                                $theSum = 0;
                                foreach ($leVl as $leV) {
                                    if (array_key_exists($leV, $v)) {
                                        $theSum += $v[$leV];
                                    }
                                }
                                if ($theSum != 0) {
                                    foreach ($leVl as $leV) {
                                        if (array_key_exists($leV, $v)) {
                                            $result[$k][$leV.'_rate'] = round($v[$leV]/$theSum, 4)*100 . '%';
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            if (!$haveing) {
                foreach ($hand as $k => $v) {
                    if (empty($restaurant[$k])) {
                        $restaurant[$k] = $v;
                    }
                }
                //计算转接率 和 参评率
                foreach ($esData['data']['aggregations']['group_by_ag_id']['buckets'] as $esVal) {
                    if ($restaurant['ag_id'] == $esVal['key']) {
                        $counts += $count = $esVal['doc_count'];
                        //参评率
                        $restaurant['evalute_rate'] = empty($count) ? '0%' : (round($restaurant['evalute1']/$count, 4)*100).'%';
                        //转接率
                        $transferRate = empty($count) ? 0 : (round(($restaurant['evalute1'] + $restaurant['evalute-3'])/$count, 4)*100);
                        if ($transferRate > 100) {
                            $restaurant['transfer_rate'] = '100%';
                        } else {
                            $restaurant['transfer_rate'] = $transferRate.'%';
                        }
                    }
                }
                //计算各种率
                foreach ($levelArr as $leVal) {
                    foreach ($leVal as $keKe=>$leVl) {
                        $theSum = 0;
                        foreach ($leVl as $leV) {
                            if (array_key_exists($leV, $restaurant)) {
                                $theSum += $restaurant[$leV];
                            }
                        }
                        if ($theSum != 0) {
                            foreach ($leVl as $leV) {
                                if (array_key_exists($leV, $restaurant)) {
                                    $restaurant[$leV.'_rate'] = round($restaurant[$leV]/$theSum, 4)*100 . '%';
                                }
                            }
                        }
                    }
                }
                $result[] = $restaurant;
            }
        };
        unset($document);
        //参评率
        $footerData['evalute_rate'] = empty($counts) ? '0%' : (round($footerData['evalute1']/$counts, 4)*100).'%';
        //转接率
        $transferRate2 = empty($counts) ? '0' : (round(($footerData['evalute1'] + $footerData['evalute-3'])/$counts, 4)*100);
        if ($transferRate2 > 100) {
            $footerData['transfer_rate'] = '100%';
        } else {
            $footerData['transfer_rate'] = $transferRate2.'%';
        }
        //累加数组中的值 计算合计的百分比
        foreach ($levelArr as $key=>$val) {
            foreach ($val as $ke=>$vl) {
                $theSum = 0;
                foreach ($vl as $v) {
                    if (array_key_exists($v, $footerData)) {
                        $theSum += $footerData[$v];
                    }
                }
                if ($theSum != 0) {
                    foreach ($vl as $v) {
                        if (array_key_exists($v, $footerData)) {
                            $footerData[$v.'_rate'] = round($footerData[$v]/$theSum, 4)*100 . '%';
                        }
                    }
                }
            }
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => array_values($result),
            'footer' => $footerData,
        );
    }

    /**
     * 短信明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getSmsListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);

        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        /** @var  $wintelapi_sms (短信接口地址) */
        $wintelapi_sms = $this->container->getParameter('wintelapi_sms');
        /** @var  $sms_flag (短信标记，用于获取不同平台的短信) */
        $sms_flag = $this->container->getParameter('sms_flag');
        $info['filter']['flag'] = $sms_flag;

        /** 判断是否是导出，如果是导出不分页 */
        if (empty($info['export'])) {
            $info['pagination']['page'] = empty($info['pagination']["page"]) ?
                1 : $info['pagination']["page"];
            $info['pagination']['rows'] = empty($info['pagination']["rows"]) ?
                10 : $info['pagination']["rows"];
        } else {
            $info['pagination']['page'] = 1;
            $info['pagination']['rows'] = 10000;
        }

        /** 排序条件 */
        $info['sort']['field'] = empty($info['sort']["sort"]) ? "id" : $info['sort']["sort"];
        $info['sort']['order'] = empty($info['sort']["order"]) ? "DESC" : $info['sort']["order"];

        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');

        /** 查询条件 */
        if (!empty($info['filter']["start_time"])) {
            $info['filter']['start_time'] = $info['filter']["start_time"];
            if (isset($time['startTime'])) {
                $info['filter']['start_time'] = $time['startTime'];
            }
        }
        if (!empty($info['filter']["end_time"])) {
            $info['filter']['end_time'] = $info['filter']["end_time"];
            if (isset($time['endTime'])) {
                $info['filter']['end_time'] = $time['endTime'];
            }
        }
        if (!empty($info['filter']["phone"])) {
            $info['filter']['phone'] = $info['filter']["phone"];
        }
        if (isset($info['filter']["result"]) && $info['filter']["result"] != -1) {
            $info['filter']['result'] = $info['filter']["result"];
        }
        $params = array(
            'vcc_code' => $vccCode,
            'info' => json_encode($info),
        );
        if ($time == 'no') {
            $params['vcc_code'] = 0;
        }
        $client = new Client();
        $request = $client->post($wintelapi_sms.'/sms/list', array(), $params);
        $response = $request->send()->json();

        if (json_last_error() !== JSON_ERROR_NONE) {
            return array(
                'code' => 404,
                'message' => '解析结果出错，错误为【'.json_last_error().'】',
            );
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $code = isset($response['code']) ? $response['code'] : 0;
        $message = isset($response['message']) ? $response['message'] : 0;
        $total = isset($response['total']) ? $response['total'] : '';
        $data = isset($response['data']) ? $response['data'] : array();
        if (!empty($data)) {
            foreach ($data as $key => $val) {
                $data[$key]['phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($val['phone'], $cusPhoneAuthority);
            }
        }
        if ($code == 200) {
            $limit = isset($info['pagination']['rows']) ? $info['pagination']['rows'] : 1000;
            $total_pages = ceil($total / $limit);

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $total,
                'page' => $info['pagination']['page'],
                'total_pages' => $total_pages,
                'data' => $data,
            );
        } else {
            return array(
                'code' => 405,
                'message' => $message,
            );
        }
    }

    /**
     * 坐席签到明细报表数据
     * @param array $param
     * @return array|string
     */
    public function getSignInData(array $param = array())
    {
        $vccId = empty($param['vcc_id']) ? 0 : $param['vcc_id'];
        $info = empty($param['info']) ? '' : $param['info'];
        $export = empty($param['export']) ? '' : $param['export'];
        $agCondition['vcc_id'] = $vccId;
        $agCondition['type'] = 'day';
        $agCondition['condition'] = '';
        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $where = ' vcc_id = :vccId ';
        $where2 = ' vcc_id = :vccId ';
        $whereData['vccId'] = $vccId;
        if (isset($addInfo['filter'])) {
            //开始日期
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $startTime = $addInfo['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startTime, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and rep_date >= :start_time ";
                $where2 .= " and nowdate >= :start_time ";
                $whereData['start_time'] = $startTime;
                $uStartTime = strtotime($startTime);
                $agCondition['condition'] .= ' AND start_time >= '.$uStartTime;
            }
            //结束日期
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $endTime = $addInfo['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endTime, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and rep_date <= :end_time ";
                $where2 .= " and nowdate <= :end_time ";
                $whereData['end_time'] = $endTime;
                $uEndTime = (int) strtotime($endTime)+86399;
                $agCondition['condition'] .= ' AND start_time <= '.$uEndTime;
            }
            //坐席ID
            if (isset($addInfo['filter']['ag_id']) && !empty($addInfo['filter']['ag_id']) && $addInfo['filter']['ag_id'] != -1) {
                $agId = $addInfo['filter']['ag_id'];
                $where .= " and ag_id = :ag_id ";
                $where2 .= " and ag_id = :ag_id ";
                $whereData['ag_id'] = $agId;
            }
            //开始时间
            if (isset($addInfo['filter']['start_hour']) && !empty($addInfo['filter']['start_hour'])) {
                $startHour = $addInfo['filter']['start_hour'];
                $msg = $this->container->get('icsoc_data.helper')->isDateHour('开始时间不正确', $startHour, 406);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and login_time >= :start_hour ";
                $whereData['start_hour'] = $startHour;
                $whereData['start_hour2'] = $startHour;
            }
            if (isset($addInfo['filter']['end_hour']) && !empty($addInfo['filter']['end_hour'])) {
                $endHour = $addInfo['filter']['end_hour'];
                $msg = $this->container->get('icsoc_data.helper')->isDateHour('结束时间不正确', $endHour, 407);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and login_time <= :end_hour ";
                $whereData['end_hour'] = $endHour;
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($addInfo['filter']['ag_id']) && $addInfo['filter']['ag_id'] == -1) {
                $where .= $authority;
            }
        }

        $dbal = $this->container->get('doctrine.dbal.default_connection');
        $count = $dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_work '.
            'WHERE '.$where,
            $whereData
        );
        //获取致忙原因因
        $agCondition['reason'] = $agStaReason = $this->container->get('icsoc_data.model.report')->getAgentStaReason($vccId);
        $page = $this->container->get("icsoc_data.helper")->getPageInfo($count, 'rep_agent_work', $addInfo);
        $list = array();
        if (empty($export)) {
            $list = $dbal->fetchAll('SELECT id,ag_num,ag_name,rep_date,login_ip,login_time,logout_time,ag_id ' .
                'FROM rep_agent_work ' .
                'WHERE ' . $where .
                ' ORDER BY ' . $page['sort'] . ' ' . $page['order'] .',login_time desc '. ' LIMIT ' . $page['start'] . ',' . $page['limit'],
                $whereData
            );
        } else if(!empty($export)) {
            $list = $dbal->fetchAll('SELECT id,ag_num,ag_name,rep_date,login_ip,login_time,logout_time,ag_id ' .
                'FROM rep_agent_work ' .
                'WHERE ' . $where .
                'ORDER BY ' . $page['sort'] . ' ' . $page['order'].',login_time desc ',
                $whereData
            );
        }
        //查询各个时长
        $timeArr = $dbal->fetchAll(
            'SELECT ag_id,nowdate,login_secs, ready_secs, busy_secs 
            FROM rep_agent_day
            WHERE ' . $where2,
            $whereData
        );
        $result = array();
        foreach ($list as $val) {
            $val['login_secs'] = 0;
            $val['ready_secs'] = 0;
            $val['busy_secs'] = 0;
            foreach ($timeArr as $vl) {
                if ($val['ag_id'] == $vl['ag_id'] && $val['rep_date'] == $vl['nowdate']) {
                    $val['login_secs'] = round($vl['login_secs']/3600, 2);
                    $val['ready_secs'] = round($vl['ready_secs']/3600, 2);
                    $val['busy_secs'] = round($vl['busy_secs']/3600, 2);
                }
            }
            $result[] = $val;
        }
        $agStaDetail = $this->container->get('icsoc_data.model.report')->getAgentStaDetail($agCondition);
        $data = array();
        foreach ($result as $key => $v) {
            foreach ($agStaReason as $reasonId => $reasonName) {
                $v['agstanum'.$reasonId] = 0;
                $v['agstaduration'.$reasonId] = 0;
                if (isset($agStaDetail[$v['rep_date']][$v['ag_id']][$reasonId]['num'])) {
                    $v['agstanum'.$reasonId] = $agStaDetail[$v['rep_date']][$v['ag_id']][$reasonId]['num'];
                }

                if (isset($agStaDetail[$v['rep_date']][$v['ag_id']][$reasonId]['secs'])) {
                    $v['agstaduration'.$reasonId] = $agStaDetail[$v['rep_date']][$v['ag_id']][$reasonId]['secs'];
                }
            }
            $v['agstanum_other'] = isset($agStaDetail[$v['rep_date']][$v['ag_id']]['other']['num']) ?
                $agStaDetail[$v['rep_date']][$v['ag_id']]['other']['num'] : 0;
            $v['agstaduration_other'] = isset($agStaDetail[$v['rep_date']][$v['ag_id']]['other']['secs']) ?
                $agStaDetail[$v['rep_date']][$v['ag_id']]['other']['secs'] : 0;
            foreach ($v as $sk=>$sv) {
                if (strstr($sk, 'agstaduration')) {
                    $v[$sk] = round($sv/3600, 2);
                } else {
                    $v[$sk] = $sv;
                }
            }
            $data[$key] = $v;
            $data[$key]['date'] = $v['rep_date'];
        }
        $param2 = array(
            'data' => $data,
            'report_name' => 'agent',
        );
        $gridData = array();
        $rst = $this->container->get('icsoc_report.model.report')->getDataReport($param2);
        if (isset($rst['code']) && $rst['code'] == 200) {
            $gridData = $rst['data'];
        }

        $ret = array(
            'code' => 200,
            'message' =>'ok',
            'total' => $count,
            'page' => $page['page'],
            'totalPage' => $page['totalPage'],
            'data' => $gridData,
        );

        return $ret;
    }

    /**
     * 坐席签到数据报表数据
     * @param array $param
     * @return array|string|void
     */
    public function getAgentWorkData(array $param = array())
    {
        $vccId = empty($param['vcc_id']) ? 0 : $param['vcc_id'];
        $info = empty($param['info']) ? '' : $param['info'];
        $agCondition['vcc_id'] = $vccId;
        $agCondition['type'] = 'day';
        $agCondition['condition'] = '';
        $pdoParam = array();
        //搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $where = ' vcc_id = ? ';
        $where2 = ' vcc_id = ? ';
        $whereData[] = $vccId;
        if (!isset($addInfo['filter'])) {
            return array('code' => 404, 'message' => '查询条件不齐全');
        } else {
            //开始日期
            if (isset($addInfo['filter']['start_date']) && !empty($addInfo['filter']['start_date'])) {
                $startDate = $addInfo['filter']['start_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and rep_date >= ? ";
                $where2 .= " and nowdate >= ? ";
                $whereData[] = $startDate;
                $uStartTime = strtotime($startDate);
                $agCondition['condition'] .= ' AND start_time >= '.$uStartTime;
            }
            //结束日期
            if (isset($addInfo['filter']['end_date']) && !empty($addInfo['filter']['end_date'])) {
                $endDate = $addInfo['filter']['end_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= " and rep_date <= ? ";
                $where2 .= " and nowdate <= ? ";
                $whereData[] = $endDate;
                $uEndTime = (int) strtotime($endDate)+86399;
                $agCondition['condition'] .= ' AND start_time <= '.$uEndTime;
            }
            //坐席ID
            if (!empty($addInfo['filter']['ag_id']) && $addInfo['filter']['ag_id'] != 'all') {
                $agId = $addInfo['filter']['ag_id'];
                $agArr = explode(',', $agId);
                $where .= " and ag_id in(?)";
                $where2 .= " and ag_id in(?)";
                $whereData[] = $agArr;
                $pdoParam = array(\PDO::PARAM_INT, \PDO::PARAM_STR, \PDO::PARAM_STR, Connection::PARAM_INT_ARRAY);
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($addInfo['filter']['ag_id']) && $addInfo['filter']['ag_id'] == 'all') {
                $where .= $authority;
                $where2 .= $authority;
                $pdoParam = array(\PDO::PARAM_INT, \PDO::PARAM_STR, \PDO::PARAM_STR);
            }
        }
        //获取致忙原因因
        $agCondition['reason'] = $agStaReason = $this->container->get('icsoc_data.model.report')->getAgentStaReason($vccId);
        $list = $this->dbal->fetchAll(
            'SELECT count(*)as sums,ag_id 
             FROM rep_agent_work
             WHERE ' . $where .
            ' GROUP BY ag_num',
            $whereData,
            $pdoParam
        );
        $timeData= $this->dbal->fetchAll("
                SELECT ag_name,ag_num,ag_id,nowdate,SUM(login_secs) AS sum_login_secs, SUM(ready_secs) AS sum_ready_secs, SUM(busy_secs) AS sum_busy_secs
                FROM rep_agent_day 
                WHERE ".$where2." GROUP BY ag_id",
            $whereData,
            $pdoParam
        );
        //获取致忙时间
        $agStaDetail = $this->container->get('icsoc_data.model.report')->getAgentStaDetail($agCondition);
        $result = array();
        foreach ($list as $key=>$val) {
            foreach ($timeData as $vl) {
                if ($val['ag_id'] == $vl['ag_id']) {
                    $vl['login_hour'] = round($vl['sum_login_secs']/3600, 2);
                    $vl['ready_hour'] = round($vl['sum_ready_secs']/3600, 2);
                    $vl['busy_hour'] = round($vl['sum_busy_secs']/3600, 2);
                    $vl['sums'] = $val['sums'];
                    $result[] = $vl;
                }
            }
        }
        $data = array();
        $agStaDetailTotal = array();
        foreach ($agStaDetail as $val) {
            foreach ($val as $key => $v) {
                foreach ($v as $k=>$dt) {
                    if (isset($agStaDetailTotal[$key][$k])) {
                        $agStaDetailTotal[$key][$k]['num']+= $dt['num'];
                        $agStaDetailTotal[$key][$k]['secs']+= $dt['secs'];
                    } else {
                        $agStaDetailTotal[$key][$k]['num'] = $dt['num'];
                        $agStaDetailTotal[$key][$k]['secs'] = $dt['secs'];
                    }
                }
            }
        }
        foreach ($result as $key => $v) {
            foreach ($agStaReason as $reasonId => $reasonName) {
                $v['agstanum'.$reasonId] = 0;
                $v['agstaduration'.$reasonId] = 0;
                if (isset($agStaDetailTotal[$v['ag_id']][$reasonId]['num'])) {
                    $v['agstanum'.$reasonId] = $agStaDetailTotal[$v['ag_id']][$reasonId]['num'];
                }

                if (isset($agStaDetailTotal[$v['ag_id']][$reasonId]['secs'])) {
                    $v['agstaduration'.$reasonId] =round($agStaDetailTotal[$v['ag_id']][$reasonId]['secs']/3600, 2);
                }
            }
            $v['agstanum_other'] = isset($agStaDetailTotal[$v['ag_id']]['other']['num']) ?
                $agStaDetailTotal[$v['ag_id']]['other']['num'] : 0;
            $v['agstaduration_other'] = isset($agStaDetailTotal[$v['ag_id']]['other']['secs']) ?
                round($agStaDetailTotal[$v['ag_id']]['other']['secs']/3600, 2) : 0;
            $data[$key] = $v;
            $data[$key]['date'] = $v['nowdate'];
        }

        $sumData = array();
        $footer['ag_name'] = '合计:';
        $footer['login_hour'] = 0;
        $footer['ready_hour'] = 0;
        $footer['busy_hour'] = 0;
        $footer['sum'] = 0;
        foreach ($data as $key=>$val) {
            $sumData[$key] = $val;
            $sumData[$key]['sum'] = $val['sums'];
            $sumData[$key]['ag_name'] = $val['ag_name'];
            $sumData[$key]['ag_num'] = $val['ag_num'];
            $sumData[$key]['ag_id'] = $val['ag_id'];
            $sumData[$key]['login_hour'] = $val['login_hour'];
            $sumData[$key]['ready_hour'] = $val['ready_hour'];
            $sumData[$key]['busy_hour'] = $val['busy_hour'];
            $footer['sum'] += $val['sums'];
            unset($val['sums']);
            unset($val['sum_login_secs']);
            unset($val['sum_ready_secs']);
            unset($val['sum_busy_secs']);
            unset($val['ag_num']);
            unset($val['ag_id']);
            unset($val['ag_name']);
            unset($val['date']);
            unset($val['nowdate']);
            foreach ($val as $k => $v) {
                if (!empty($footer[$k])) {
                    if (strstr($k, 'agstaduration') || $k == 'login_hour' || $k == 'ready_hour' || $k == 'busy_hour') {
                        $footer[$k] = bcadd($footer[$k], $v, 2);
                    } else {
                        $footer[$k] += empty($v) ? 0 : $v;
                    }
                } else {
                    $footer[$k] = empty($v) ? 0 : $v;
                }
            }
        }

        $ret = array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($sumData),
            'data' => $sumData,
            'footer' => $footer,
        );

        return $ret;
    }

    /**
     * 通话重复号码统计报表数据(es)
     * @param array $param
     * @return array|string|void
     */
    public function getEsRepetitionPhoneData(array $param = array())
    {
        $vccId = empty($param['vcc_id']) ? 0 : $param['vcc_id'];
        $info = empty($param['info']) ? '' : $param['info'];
        $size = 2000;   //最多显示条数
        //搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        $field = empty($info['sort']['field']) ? 'start_time' : $info['sort']['field'];
        $esCondition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => "win_agcdr",
                'order' => $order,
                'field' => $field,
                'size' => 0,
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
            ),
            'aggs'=>array(
                'group_by_cus_phone'=>array(
                    'terms'=>array(
                        'field'=>'cus_phone',
                        'size'=>$size,
                        'shard_size'=>10000,
                    ),
                ),
            )
        );
        $inLine = '';
        if (!isset($addInfo['filter'])) {
            return array('code' => 404, 'message' => '查询条件不齐全');
        } else {
            //号码
            if (isset($addInfo['filter']['phone']) && !empty($addInfo['filter']['phone'])) {
                $esCondition['term']['cus_phone'] = array('type' => 'match', 'value' => $addInfo['filter']['phone']);
            }
            //呼叫类型
            if (isset($addInfo['filter']['call_type']) && $addInfo['filter']['call_type'] != -1) {
                $esCondition['term']['call_type'] = array('type' => 'match', 'value' => $addInfo['filter']['call_type']);
                $esCondition['aggs']['group_by_cus_phone']['aggs']['froup_by_result'] = array(
                    'terms'=>array(
                        'field'=>'result',
                    )
                );
            } else if(isset($addInfo['filter']['call_type']) && $addInfo['filter']['call_type'] == -1){
                //查询全部呼叫类型 就按照call_type再次分类
                $esCondition['aggs']['group_by_cus_phone']['aggs']['group_by_call_type'] = array(
                    'terms'=>array(
                        'field'=>'call_type',
                    ),
                    'aggs'=>array(
                        'group_by_result'=> array(
                            'terms'=>array(
                                'field'=>'result',
                            )
                        )
                    )
                );
            }
            //日期
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $startDate = $addInfo['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $esCondition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
                $esCondition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($startDate) + 86399, 'field' => 'start_time', 'operation' => 'lte');
            } else {
                return array('code' => 404, 'message' => '日期不存在');
            }
            //结束时间
            /*if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $endDate = $addInfo['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $esCondition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            } else {
                return array('code' => 405, 'message' => '结束时间不存在');
            }*/
            //最少次数
            if (isset($addInfo['filter']['least_count']) && !empty($addInfo['filter']['least_count'])) {
                $inLine .= " sum >= ".$addInfo['filter']['least_count'];
            } else {
                return array('code' => 406, 'message' => '最少次数不存在');
            }
            //最多次数
            if (isset($addInfo['filter']['most_count']) && !empty($addInfo['filter']['most_count']) && $addInfo['filter']['call_type'] != -1) {
                $inLine .= " && sum <= ".$addInfo['filter']['most_count'];
            }
            $esCondition['aggs']['group_by_cus_phone']['aggs']['having'] = array(
                'bucket_selector'=>array(
                    'buckets_path'=>array(
                        'sum'=>'_count',
                    ),
                    'script'=>array(
                        'inline'=>$inLine,
                    ),
                ),
            );
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id'])) {
                $esCondition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id'])) {
                $esCondition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id'])) {
                $esCondition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        //处理条件数组便于直接查询
        $esCondition = $this->container->get('icsoc_data.model.record')->getEsSearch($esCondition);
        unset($esCondition['body']['sort']);
        try {
            $hosts = $this->container->getParameter('elasticsearch_hosts');
            $client = new EsClient(array('hosts' => $hosts));
            $result = $client->search($esCondition);
            $data = array();
            if (!empty($result['aggregations']['group_by_cus_phone']['buckets'])) {
                //呼叫类型
                $callType = $this->container->getParameter('CALLTYPE');
                foreach ($result['aggregations']['group_by_cus_phone']['buckets'] as $key => $val) {
                    if (empty($val['key'])) {
                        continue;
                    }
                    /*if ($addInfo['filter']['call_type'] == -1) {
                        foreach ($val['group_by_call_type']['buckets'] as $v) {
                            if ($v['key'] != 1 && $v['key'] != 2) {
                                continue;
                            }
                            if ($v['doc_count'] < $addInfo['filter']['least_count']) {
                                continue;
                            }
                            if (isset($addInfo['filter']['most_count']) && !empty($addInfo['filter']['most_count']) && $v['doc_count'] > $addInfo['filter']['most_count']) {
                                continue;
                            }
                            $set = array();
                            $set['phone'] = $val['key'];   //号码
                            $set['call_type'] = empty($callType[$v['key']]) ? '' : $callType[$v['key']];     //通话类型
                            $set['dial_num'] = $v['doc_count']; //拨号数量
                            $set['connect'] = 0;
                            foreach ($v['froup_by_result']['buckets'] as $sv) {
                                if ($sv['key'] != 0) {
                                    continue;
                                }
                                $set['connect'] = $sv['doc_count'];  //接通数量
                            }
                            $set['connect_rate'] = round($set['connect']/$set['dial_num'], 4)*100 . '%';
                            $data[] = $set;
                            if (count($data) >= $size) {
                                break;
                            }
                        }
                    } else {*/
                        $set = array();
                        $set['phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($val['key'], $cusPhoneAuthority);   //号码
                        $set['call_type'] = empty($callType[$addInfo['filter']['call_type']]) ? '' : $callType[$addInfo['filter']['call_type']];     //通话类型
                        $set['dial_num'] = $val['doc_count']; //拨号数量
                        $set['connect'] = 0;
                        foreach ($val['froup_by_result']['buckets'] as $sv) {
                            if ($sv['key'] != 0) {
                                continue;
                            }
                            $set['connect'] = $sv['doc_count'];  //接通数量
                        }
                        $set['connect_rate'] = round($set['connect']/$set['dial_num'], 4)*100 . '%';
                        $data[] = $set;
                        if (count($data) >= $size) {
                            break;
                        }
                    //}
                }
            }
            unset($result);
        } catch (\Exception $e) {
            $logger = $this->container->get('logger');
            $logger->error($e->getMessage());

            return array(
                'code' => 500,
                'message' => '发生异常',
                'count' => 0,
            );
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($data),
            'data' => $data,
        );
    }

    /**
     * 通话重复号码统计报表数据(mongodb)
     * @param array $param
     * @return array|string|void
     */
    public function getMgRepetitionPhoneData(array $param = array())
    {
        $vccId = empty($param['vcc_id']) ? 0 : $param['vcc_id'];
        $info = empty($param['info']) ? '' : $param['info'];
        //搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $mgCondition = array();
        $match['$match'] = array('vcc_id' => (int) $vccId);
        if (!isset($addInfo['filter'])) {
            return array('code' => 404, 'message' => '查询条件不齐全');
        } else {
            //号码
            if (isset($addInfo['filter']['phone']) && !empty($addInfo['filter']['phone'])) {
                $match['$match']['phone'] = (string) $addInfo['filter']['phone'];
            }
            //呼叫类型
            if (isset($addInfo['filter']['call_type']) && $addInfo['filter']['call_type'] != -1) {
                $match['$match']['call_type'] = (int) $addInfo['filter']['call_type'];
            }
            //开始时间
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $startDate = $addInfo['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始时间不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $match['$match']['date']['$gte'] = (string) $startDate;
            } else {
                return array('code' => 404, 'message' => '开始时间不存在');
            }
            //结束时间
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $endDate = $addInfo['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束时间不正确', $endDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $match['$match']['date']['$lte'] = (string) $endDate;
                if (strtotime($addInfo['filter']['end_time']) - strtotime($addInfo['filter']['start_time']) > 2592000) {
                    return array('code' => 407, 'message' => '时间间隔超过31天');
                }
            } else {
                return array('code' => 405, 'message' => '结束时间不存在');
            }
            //最少次数
            if (isset($addInfo['filter']['least_count']) && !empty($addInfo['filter']['least_count'])) {
                $match['$match']['total_num']['$gte'] = (int) $addInfo['filter']['least_count'];
            } else {
                return array('code' => 406, 'message' => '最少次数不存在');
            }
            //最多次数
            if (isset($addInfo['filter']['most_count']) && !empty($addInfo['filter']['most_count'])) {
                $match['$match']['total_num']['$lte'] = (int) $addInfo['filter']['most_count'];
            }
        }
        $group['$group'] = array(
            '_id' => '$phone',
            'total_num' => array(
                '$sum' => '$total_num',
            ),
            'con_num' => array(
                '$sum' => '$con_num',
            ),
            'nocon_num' => array(
                '$sum' => '$nocon_num',
            ),
            'phone' => array(
                '$first' => '$phone',
            ),
            'call_type' => array(
                '$first' => '$call_type',
            )
        );
        $mgCondition[] = $match;
        $mgCondition[] = $group;
        $countMgCondition[] = $match;
        $countMgCondition[] = $group;
        $sort['$sort'] = array('total_num' => (int) -1);  //排序
        $limit['$limit'] = (int) $addInfo['sort']['rows'];   //取前几条
        $mgCondition[] = $sort;
        $mgCondition[] = $limit;
        $document = $this->container->get('icsoc_core.mongodb_common.class')->getDataForCharts($mgCondition, 'repeat_phone_num');
        //查看是否拥有查看客户号码权限
        $cusPhoneAuthority = $this->container->get("icsoc_core.common.class")->getWhetherHaveAuthority('icsoc_show_cus_phone');
        $callType = $this->container->getParameter('CALLTYPE');
        $data = array();
        foreach($document as $restaurant) {
            $restaurant = get_object_vars($restaurant);
            $set = array();
            $set['phone'] = $this->container->get("icsoc_core.common.class")->concealCusPhone($restaurant['phone'], $cusPhoneAuthority);   //号码
            $set['call_type'] = empty($callType[$restaurant['call_type']]) ? '' : $callType[$restaurant['call_type']];     //通话类型
            $set['dial_num'] = $restaurant['total_num'];
            $set['connect'] = $restaurant['con_num'];
            $set['no_connect'] = $restaurant['nocon_num'];
            $set['connect_rate'] = round($set['connect']/$set['dial_num'], 4)*100 . '%';
            if (isset($addInfo['filter']['most_count']) && !empty($addInfo['filter']['most_count'])) {
                if ($restaurant['total_num'] > $addInfo['filter']['most_count']) {
                    continue;
                }
            }
            $data[] = $set;
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($data),
            'data' => $data,
        );
    }

    /**
     * 当连接ads数据库时，获取分页的查询条件
     *
     * @param $forPageParam array
     * @param $limit int
     * @param $count  int
     * @return array
     */
    public function pageForAdsSearch($forPageParam, $limit, $count)
    {
        $where = '';
        $totalPage = ceil($count / $limit);
        $orderBy = ' ORDER BY id DESC ';
        $sqlLimit = "LIMIT 10000";
        if (isset($limit) && !empty($limit)) {
            $sqlLimit = ' LIMIT '.$limit;
        }
        $isReversed = false;
        $isChangeRowlist = false;
        if (!empty($forPageParam)) {
            //由于是倒序排序，所以lastId是最小的id,firstId是最大的id
            $lastId = $forPageParam['lastId'];
            $firstId = $forPageParam['firstId'];
            $action = $forPageParam['pgButton'];
            if ($action == 'prev_grid-pager') {//上一页
                $where .= ' AND id > '.$firstId;
                $orderBy = ' ORDER BY id ASC ';
                $sqlLimit = ' LIMIT '.$limit;
                $isReversed = true;
            } else if ($action == 'next_grid-pager') {//下一页
                $where .= ' AND id < '.$lastId;
                $orderBy = ' ORDER BY id DESC ';
                $sqlLimit = ' LIMIT '.$limit;
            } else if ($action == 'first_grid-pager') {//第一页
                $orderBy = ' ORDER BY id DESC ';
                $sqlLimit = ' LIMIT '.$limit;
            } else if ($action == 'last_grid-pager') {//最后一页
                $mod = $count % $limit;
                $orderBy = ' ORDER BY id ASC ';
                if ($mod != 0) {
                    $sqlLimit = ' LIMIT '.$mod;
                } else {
                    $sqlLimit = ' LIMIT '.$limit;
                }
                $isReversed = true;
            } else if ($action == 'records') {
                $isChangeRowlist = true;
            }
        }

        return array('where' => $where, 'orderBy' => $orderBy, 'sqlLimit' => $sqlLimit, 'totalPage' => $totalPage, 'isReversed' => $isReversed, 'isChangeRowlist' => $isChangeRowlist);
    }

    /**
     * 根句条件在elasticsearch和mongodb中获取数据
     *
     * @param $condition
     * @param $mongoColumn
     * @param array $order
     * @return array
     */
    public function getDataFromESAndMongo($condition, $mongoColumn, $order = array('field' => '_id', 'order' => 'desc'))
    {
        //从elasticsearch中查询出满足条件的数据的ID
        $data = $this->container->get('icsoc_data.model.record')->searchData($condition);
        $res = $data['data'];
        $page = $data['page'];
        $count = empty($data['count']) ? 0 : $data['count'];
        $totalPages = $data['total'];
        $ids = array();
        foreach ($res as $k => $re) {
            $ids[] = $re['id'];
            unset($res[$k]);
        }
        $params['allIds'] = $ids;
        unset($data);
        unset($res);
        unset($ids);

        //从mongodb中，通过ID获取数据的值
        $result = $this->container->get('icsoc_core.mongodb_common.class')->getDataForPage($params, $mongoColumn);
        $rows = array();

        foreach ($result as $val) {
            $rows[] = $val;
        }
        unset($result);

        $arr = array();
        foreach ($rows as $k => $v) {
            $arr[$k] = $v[$order['field']];
        }
        if ($order['order'] == 'desc') {
            arsort($arr);
        } else {
            asort($arr);
        }
        $resultData = array();
        //根据规定的字段倒序排序
        foreach ($arr as $k => $val) {
            $resultData[] = $rows[$k];
        }
        unset($arr);
        unset($rows);
        unset($params);

        return array('page' => $page, 'count' => $count, 'totalPages' => $totalPages, 'data' => $resultData);
    }

    /**
     * @param $vccId
     * @return string
     */
    public function getAgentsForChosenSelect($vccId)
    {
        $vccId = (int) $vccId;
        $condition = $this->container->get('icsoc_core.common.class')->getAuthorityCondition('id', 'que_id', 2);
        $agents = $this->dbal->fetchAll(
            "select id,ag_num,ag_name from win_agent where vcc_id = :vcc_id and is_del = 0 $condition",
            array('vcc_id' => $vccId)
        );

        $result = '';
        foreach ($agents as $key => $agent) {
            $result .= '<option value="'.$agent['id'].'">'.$agent['ag_num'].' '.$agent['ag_name'].'</option>';
        }

        return $result;
    }

    /**
     * 明细报表处理callID
     *
     * @param $callId
     * @param $index
     * @param $replaceStr
     * @return mixed
     */
    public function processCallId($callId)
    {
        if (strpos($callId, '100000000') !== false) {
            $callId = str_replace('100000000', 'def_', $callId);
        }
        if (strpos($callId, '200000000') !== false) {
            $callId = str_replace('200000000', 'yd_', $callId);
        }
        if (strpos($callId, '300000000') !== false) {
            $callId = str_replace('300000000', 'callbj_', $callId);
        }
        if (strpos($callId, '400000000') !== false) {
            $callId = str_replace('400000000', 'callesn_', $callId);
        }

        return $callId;
    }
}
